package com.chen.services;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import com.chen.dao.HibernateSessionFactory;
import com.chen.model.Base;
import com.chen.util.BaseUtil;
@SuppressWarnings("unchecked")
public class BaseServiceImpl implements BaseService {

	
	@Override
	public List<Base> query() throws Exception {
		//String sqlstring = "";
		List list = HibernateSessionFactory.currentSession().createSQLQuery("select * from base").addEntity(Base.class).list();
		HibernateSessionFactory.closeSession();
		return list;
	}
	
	@Override
	public List<Base> queryByNo(String c,String s) throws Exception {
		if(BaseUtil.isBlank(s)){
			return queryAll();
		}
		List list = HibernateSessionFactory.currentSession().createSQLQuery("select * from base where "+ c +" like '%"+s+"%'").addEntity(Base.class).list();
		List sum = HibernateSessionFactory
				.currentSession()
				.createSQLQuery(
						"select 'job_no' as job_no,'name' as name,'postion' as postion,'bclass' as bclass,0.00 as payfactor,'job_no2' as job_no2,"
								+ "sum(nh) as nh,sum(np) as np,sum(oh) as oh,sum(op) as op,sum(sh) as sh,sum(sp) as sp,sum(hh) as hh,sum(hp) as hp,"
								+ "sum(bonus) as bonus,sum(gross) as gross,sum(nssa) as nssa,sum(paye) as paye,sum(aids) as aids,sum(nec) as nec,sum(total) as total,sum(net) as net from base where "
								+ c + " like '%" + s + "%'")
				.addEntity(Base.class).list();
		list.addAll(sum);
		HibernateSessionFactory.closeSession();
		return list;
	}

	public List<Base> queryAll() throws Exception {
		List list = HibernateSessionFactory.currentSession().createSQLQuery("select * from base").addEntity(Base.class).list();
		List sum = HibernateSessionFactory
		.currentSession()
		.createSQLQuery(
				"select 'job_no' as job_no,'name' as name,'postion' as postion,'bclass' as bclass,0.00 as payfactor,'job_no2' as job_no2,"
						+ "sum(nh) as nh,sum(np) as np,sum(oh) as oh,sum(op) as op,sum(sh) as sh,sum(sp) as sp,sum(hh) as hh,sum(hp) as hp,"
						+ "sum(bonus) as bonus,sum(gross) as gross,sum(nssa) as nssa,sum(paye) as paye,sum(aids) as aids,sum(nec) as nec,sum(total) as total,sum(net) as net from base")
		.addEntity(Base.class).list();
list.addAll(sum);
		HibernateSessionFactory.closeSession();
		return list;
	}

	@Override
	public boolean save(Base base) throws Exception {
		Session session = HibernateSessionFactory.currentSession();
		Transaction tx = HibernateSessionFactory.currentSession().beginTransaction();
		session.saveOrUpdate(base);
		tx.commit();
		HibernateSessionFactory.closeSession();
		return true;
	}

	@Override
	public boolean delete(Base base) throws Exception {
		Session session = HibernateSessionFactory.currentSession();
		Transaction tx = HibernateSessionFactory.currentSession().beginTransaction();
		session.delete(base);
		tx.commit();
		HibernateSessionFactory.closeSession();
		return true;
	}

}
